package com.example.teemor.es_demo.repository.jpa;

import com.example.teemor.es_demo.entity.Order;
import com.example.teemor.es_demo.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    /**
     * 根据订单号查找订单
     */
    Optional<Order> findByOrderNo(String orderNo);
    
    /**
     * 根据用户查找订单
     */
    List<Order> findByUserOrderByCreatedAtDesc(User user);
    
    /**
     * 根据用户ID查找订单（分页）
     */
    Page<Order> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);
    
    /**
     * 根据订单状态查找订单
     */
    List<Order> findByStatus(Order.OrderStatus status);
    
    /**
     * 根据用户和状态查找订单
     */
    List<Order> findByUserAndStatus(User user, Order.OrderStatus status);
    
    /**
     * 统计用户订单数量
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.user.id = :userId")
    Long countByUserId(@Param("userId") Long userId);
    
    /**
     * 统计用户订单总金额
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.user.id = :userId AND o.status = :status")
    BigDecimal sumTotalAmountByUserIdAndStatus(@Param("userId") Long userId, @Param("status") Order.OrderStatus status);
    
    /**
     * 查找指定时间范围内的订单
     */
    @Query("SELECT o FROM Order o WHERE o.createdAt BETWEEN :startTime AND :endTime")
    List<Order> findOrdersBetweenDates(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计今日订单数量
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE CAST(o.createdAt AS date) = CURRENT_DATE")
    Long countTodayOrders();
    
    /**
     * 获取订单趋势（每日订单数）
     */
    @Query("SELECT CAST(o.createdAt AS date) as date, COUNT(o) as value " +
           "FROM Order o " +
           "WHERE o.createdAt BETWEEN :start AND :end " +
           "GROUP BY CAST(o.createdAt AS date) " +
           "ORDER BY date ASC")
    List<Map<String, Object>> getOrderTrend(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end);

    /**
     * 获取收入趋势（每日收入）
     */
    @Query("SELECT CAST(o.createdAt AS date) as date, SUM(o.totalAmount) as value " +
           "FROM Order o " +
           "WHERE o.createdAt BETWEEN :start AND :end AND o.status = 'PAID' " +
           "GROUP BY CAST(o.createdAt AS date) " +
           "ORDER BY date ASC")
    List<Map<String, Object>> getRevenueTrend(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end);

    /**
     * 统计总收入
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE o.status = :status")
    BigDecimal sumTotalAmountByStatus(@Param("status") Order.OrderStatus status);

    /**
     * 统计今日订单总金额
     */
    @Query("SELECT SUM(o.totalAmount) FROM Order o WHERE CAST(o.createdAt AS date) = CURRENT_DATE AND o.status = :status")
    BigDecimal sumTodayOrderAmount(@Param("status") Order.OrderStatus status);
}